# -*- coding:utf8 -*-
"""
表格
"""
from __future__ import print_function
import datetime
import copy
import pymysql as MySQLdb
MySQLdb.install_as_MySQLdb
import pymysql.cursors

from contextlib import closing
from logger_utility.loggconfig import getLogger

logger = getLogger("MysqlTable")


class TableBase(object):
    """
    表格基础，注意_开始的key不为数据库中的列名，以此进行区分
    """
    JSON_DATETIME = 'datetime'

    def __init__(self,
                 table,
                 primary_key,
                 db_config=None,
                 json_cover=None
                 ):
        self.table = table
        self.primary_key = primary_key
        self.db_config = copy.copy(db_config)
        self.json_cover = json_cover
        if self.db_config and self.db_config.get('port'):
            self.db_config['port'] = int(self.db_config['port'])

    def set_db_config(self, db_config):
        """
        设置DB设置
        :param db_config:
        :return:
        """
        self.db_config = copy.copy(db_config)
        if self.db_config and self.db_config.get('port'):
            self.db_config['port'] = int(self.db_config['port'])

    def _get_conn(self):
        """
        获取数据库连接
        :return:
        """
        return MySQLdb.connect(
            cursorclass=MySQLdb.cursors.DictCursor,
            **self.db_config)

    def _exce_sql(self, sql, args=tuple()):
        """
        执行实际的SQL语句
        """
        try:
            logger.debug("Execute Sql: %s, Args: %s", sql.replace('\n', ' '), repr(args))
            sql = sql.strip()

            with closing(MySQLdb.connect(
                    cursorclass=MySQLdb.cursors.DictCursor,
                    **self.db_config)) as conn:
                with closing(conn.cursor()) as cursor:

                    _ = cursor.execute(sql, args)
                    if sql.startswith('select'):
                        ret = list(dict_ for dict_ in cursor.fetchall())
                        return ret

                    # 插入和更新数量为0
                    if _ == 0:
                        logger.debug("Excute Sql Return Value: %s", _)
                        return None

                    if sql.startswith('insert') or sql.startswith('INSERT'):
                        conn.commit()
                        get_insert_id = 'select @@IDENTITY'
                        cursor.execute(get_insert_id)
                        insert_id = list(cursor.fetchall())[0]['@@IDENTITY']
                        logger.debug("Excute Insert Sql Return Value: %s", insert_id)
                        return insert_id

                    if sql.startswith('update'):
                        conn.commit()
                        return True

                    if sql.startswith('delete'):
                        conn.commit()
                        return True

        except Exception as e:
            logger.exception("Execute Sql: %s Error, Exception: %s", sql, e)
            return None

    def _exce_many(self, lines):
        """
        执行实际的SQL语句
        """
        last_ret = None

        with closing(MySQLdb.connect(
                cursorclass=MySQLdb.cursors.DictCursor,
                **self.db_config)) as conn:
            with closing(conn.cursor()) as cursor:

                for line in lines:
                    try:
                        sql, arg = line
                        logger.debug("Execute Sql: %s, Args: %s", sql, repr(arg))
                        sql = sql.strip()

                        _ = cursor.execute(sql, arg)
                        if sql.startswith('select'):
                            ret = list(dict_ for dict_ in cursor.fetchall())
                            last_ret = ret
                            continue

                        # 插入和更新数量为0
                        if _ == 0:
                            last_ret = None
                            continue

                        if sql.startswith('insert'):
                            get_insert_id = 'select @@IDENTITY'
                            cursor.execute(get_insert_id)
                            insert_id = list(cursor.fetchall())[0]['@@IDENTITY']
                            last_ret = insert_id
                            continue

                        if sql.startswith('update'):
                            last_ret = True
                            continue

                        if sql.startswith('delete'):
                            last_ret = True
                            continue

                    except Exception as e:
                        conn.rollback()
                        logger.warning("Execute Sql: %s Error, Exception: %s", sql, e)
                        return None

                conn.commit()
        return last_ret

    def exce_sql_iter(self, sql, args=tuple()):
        """
        执行实际的SQL语句
        """
        try:
            logger.debug("Execute Sql: %s", (sql, args))

            with closing(MySQLdb.connect(
                    cursorclass=MySQLdb.cursors.DictCursor, **self.db_config)) as conn:
                with closing(conn.cursor()) as cursor:

                    _ = cursor.execute(sql, args)
                    if sql.startswith('select'):
                        dict_ = cursor.fetchone()
                        if dict_:
                            yield dict_

                    if sql.startswith('insert'):
                        conn.commit()
                        get_insert_id = 'select @@IDENTITY'
                        cursor.execute(get_insert_id)
                        insert_id = list(cursor.fetchall())[0]['@@IDENTITY']
                        yield insert_id

                    if sql.startswith('update'):
                        conn.commit()
                        yield 0

                    if sql.startswith('delete'):
                        conn.commit()
                        yield 0

        except Exception as e:
            print("Execute Sql: %s Error, Exception: %s", sql, e)
            yield None

    def _dict_to_condition(self, dict_):
        """
        将字典类型的选择条件转变为SQL
        """
        sql_str = []
        value_tuple = []

        for key, value in dict_.items():
            if key[0] != '_':
                if value is None:
                    sql_str.append('{} is null'.format(key))
                else:
                    sql_str.append('{}=%s'.format(key))
                    value_tuple.append(value)

        return ' and '.join(sql_str), value_tuple

    def _dict_to_update(self, dict_):
        """
        将字典更新为插入字段
        """
        sql_str = []
        value_tuple = []

        for key, value in dict_.items():
            if key[0] != '_':
                sql_str.append('`{}`=%s'.format(key))
                value_tuple.append(value)

        return ' , '.join(sql_str), value_tuple

    def all(self):
        """
        获取全部数据
        """
        return self._exce_sql("select * from {}".format(self.table))

    def get(self, **kwargs):
        """
        获取数据
        """
        if not kwargs:
            return self.all()

        sql_condition, value_tuple = self._dict_to_condition(kwargs)
        return self._exce_sql(
            "select * from {} where {}".format(self.table, sql_condition),
            value_tuple
        )

    def limit(self, limit, number, **kwargs):
        """
        计算总的数量以及进行筛选
        :param limit:
        :param number:
        :param kwargs:
        :return:
        """
        if not kwargs:
            total = self._exce_sql("select count(*) from {}".format(self.table))[0]
            return total, self._exce_sql("select * from {} limit {}, {}".format(self.table, limit, number))

        sql_condition, value_tuple = self._dict_to_condition(kwargs)
        total = self._exce_sql(
            "select count(*) from {} where {}".format(self.table, sql_condition),
            value_tuple
        )[0]
        return total, self._exce_sql(
            "select * from {} where {} limit {}, {}".format(self.table, sql_condition, limit, number),
            value_tuple
        )


    def save(self, dict_):
        """
        保存数据
        """
        if dict_.get(self.primary_key):
            ret = self.update(dict_)
        else:
            ret = self.insert(dict_)
        return ret

    def update(self, dict_):
        """
        更新数据
        """
        sql_str, values = self._dict_to_update(dict_)
        values.append(dict_[self.primary_key])

        ret = self._exce_sql(
            "update {} set {} where {} = %s".format(self.table, sql_str, self.primary_key),
            values
        )
        if ret is None:
            return None
        else:
            return dict_[self.primary_key]

    def delete(self, dict_):
        """
        删除数据
        """
        primary_value = dict_.get(self.primary_key)
        ret = self._exce_sql(
            "delete from {} where {} = %s".format(self.table, self.primary_key),
            (primary_value, )
        )
        if ret is None:
            return None
        else:
            return primary_value

    def insert(self, dict_):
        """
        插入数据
        """
        if self.primary_key in dict_:
            del dict_[self.primary_key]

        cols, values = [], []
        for k, v in dict_.items():
            if k[0] != '_':
                cols.append(k)
                values.append(v)

        place_str = ','.join(['%s'] * len(cols))
        ret = self._exce_sql(
            "insert into {}({})values({})".format(self.table,
                                                  ','.join('`{}`'.format(i) for i in cols),
                                                  place_str),
            values
        )
        return ret

    def to_json(self, dict_):
        """
        将其中无法用json表示的字段进行更改
        """
        if not self.json_cover:
            return dict_

        # 处理时间
        datetime_cols = self.json_cover.get(self.JSON_DATETIME, [])
        for col in datetime_cols:
            if dict_.get(col):
                dict_[col] = self._to_json_datetime(dict_[col])

    def from_json(self, dict_):
        """
        恢复正常类型以存入数据库
        """
        # 处理时间
        datetime_cols = self.json_cover.get(self.JSON_DATETIME, [])
        for col in datetime_cols:
            if dict_.get(col):
                dict_[col] = self._from_json_datetime(dict_[col])
        return dict_

    def _to_json_datetime(self, date_time):
        """
        时间格式转换
        """
        return date_time.strftime('%Y-%m-%d %H:%M')

    def _from_json_datetime(self, date_time):
        """
        时间格式转换
        """
        return datetime.datetime.strptime(date_time, '%Y-%m-%d %H:%M:%s')


if __name__ == '__main__':
    table_test = TableBase(
        'user',
        'id',
        db_config={
            'host': '127.0.0.1',
            'user': 'root',
            'passwd': 'jianyong',
            'db': 'travel',
            'charset': 'utf8',
        }
    )
    for i in table_test.get():
        print(i)
